package inet.db.tennis;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import inet.db.pool.*;
import inet.util.LOBs;
import inet.util.Logger;

public class TennisPlayerDAO {

	private Logger logger = null;
	private DBPoolX poolX = null;
	public TennisPlayerDAO(){
		logger = new Logger(this.getClass().getName());
		try{
			poolX = DBPoolX.getInstance(DBPoolXName.SERVICE_BONGDA);
		}catch(Exception exception){
			logger.log(exception.toString()); 
		}
	}
	
	/**
	 * function a TennisPlayer by Id
	 * @param id
	 * @return TennisPlayer
	 */
    public TennisPlayer getRow(BigDecimal id) {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String strSQL = null;
        TennisPlayer player = null;
        try {
            conn = poolX.getConnection();
            strSQL =
            	" select a.ID, a.PLAYER_NAME, DOB, a.COUNTRY_ID, a.AVATAR, PREPERRED_HAND, WIN_OF_YEAR, LOSE_OF_YEAR, MAP_NAME, GENDER, a.LAST_UPDATED, b.COUNTRY_NAME " +
    			" from TENNIS_PLAYER a left join TENNIS_COUNTRY b ON a.COUNTRY_ID = b.ID " +
    			" where a.ID = ? ";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setBigDecimal(1, id);
            rs = preStmt.executeQuery();
            if (rs.next()) {
            	player = new TennisPlayer();
            	player.setId(rs.getBigDecimal(1));
            	player.setName(rs.getString(2));
            	player.setDob(rs.getString(3));
            	player.setCountryId(rs.getBigDecimal(4));
            	player.setAvatar(rs.getBytes(5));
            	player.setPreferredHand(rs.getString(6));
            	player.setWinOfYear(rs.getInt(7));
            	player.setLoseOfYear(rs.getInt(8));
            	player.setMapName(rs.getString(9));
            	player.setGender(rs.getInt(10));
            	player.setLastUpdated(rs.getTimestamp(11));
            	player.setNationality(rs.getString(12));
            }
        }
        catch (SQLException e) {
            logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            poolX.releaseConnection(conn, preStmt, rs);
        }
        return player;
    }
    
    /**
     * function get all player 
     * @return Vector<TennisPlayer>
     */
    public Vector<TennisPlayer> findAll() {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisPlayer player = null;
    	Vector <TennisPlayer> vPlayer = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select a.ID, a.PLAYER_NAME, DOB, a.COUNTRY_ID, a.AVATAR, PREPERRED_HAND, WIN_OF_YEAR, LOSE_OF_YEAR, MAP_NAME, GENDER, a.LAST_UPDATED, b.COUNTRY_NAME " +
    			" from TENNIS_PLAYER a left join TENNIS_COUNTRY b ON a.COUNTRY_ID = b.ID ";
    		preStmt = conn.prepareStatement(strSQL);
    		rs = preStmt.executeQuery();
    		vPlayer = new Vector<TennisPlayer>();
    		while (rs.next()) {
    			player = new TennisPlayer();
            	player.setId(rs.getBigDecimal(1));
            	player.setName(rs.getString(2));
            	player.setDob(rs.getString(3));
            	player.setCountryId(rs.getBigDecimal(4));
            	player.setAvatar(rs.getBytes(5));
            	player.setPreferredHand(rs.getString(6));
            	player.setWinOfYear(rs.getInt(7));
            	player.setLoseOfYear(rs.getInt(8));
            	player.setMapName(rs.getString(9));
            	player.setGender(rs.getInt(10));
            	player.setLastUpdated(rs.getTimestamp(11));
            	player.setNationality(rs.getString(12));
    			
            	vPlayer.addElement(player);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vPlayer;
    }

    /**
     * function insert into TENNIS_PLAYER 
     * @param playerName
     * @param dob
     * @param nationality
     * @param avatar
     * @param preperredHand
     * @param winOfYear
     * @param loseOfYear
     * @param mapName
     * @param gender
     * @return true if insert success else return false.
     */
	public boolean insertRow(String playerName, String dob, BigDecimal countryId , byte[] avatar, String preperredHand, int winOfYear, int loseOfYear, String mapName, int gender){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
            conn = poolX.getConnection();

        	strSQL =" insert into TENNIS_PLAYER( ID, PLAYER_NAME, DOB, COUNTRY_ID, AVATAR, PREPERRED_HAND, WIN_OF_YEAR, LOSE_OF_YEAR, MAP_NAME, GENDER, LAST_UPDATED ) " +
    				" values(TENNIS_PLAYER_SEQ.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate) ";
	        preStmt = conn.prepareStatement(strSQL);
	        preStmt.setString(1, playerName);
	        preStmt.setString(2, dob);
	        preStmt.setBigDecimal(3, countryId);
	        preStmt.setBytes(4, avatar);
	        preStmt.setString(5, preperredHand);
	        preStmt.setInt(6, winOfYear);
	        preStmt.setInt(7, loseOfYear);
	        preStmt.setString(8, mapName);
	        preStmt.setInt(9, gender);
	        
	        if (preStmt.executeUpdate()>=1) {
	        	result = true;
	        }
        } catch (Exception ex) {
        	logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
       return result;
	}
	
	/**
	 * function update TENNIS_PLAYER
	 * @param id
	 * @param playerName
	 * @param dob
	 * @param nationality
	 * @param preperredHand
	 * @param winOfYear
	 * @param loseOfYear
	 * @param mapName
	 * @param gender
	 * @return true if insert success else return false.
	 */
	public boolean updateRow(BigDecimal id, String playerName, String dob, BigDecimal countryId, String preperredHand, int winOfYear, int loseOfYear, String mapName, int gender){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
        	conn = poolX.getConnection();
        	strSQL =" update TENNIS_PLAYER set PLAYER_NAME = ?, DOB = ?, COUNTRY_ID = ?, PREPERRED_HAND = ?, WIN_OF_YEAR = ?, LOSE_OF_YEAR = ?, " +
        			" MAP_NAME = ?, GENDER = ?, LAST_UPDATED = sysdate " +
        			" where ID = ?";
        	preStmt = conn.prepareStatement(strSQL);
        	preStmt.setString(1, playerName);
        	preStmt.setString(2, dob);
        	preStmt.setBigDecimal(3, countryId);
        	preStmt.setString(4, preperredHand);
        	preStmt.setInt(5, winOfYear);
        	preStmt.setInt(6, loseOfYear);
        	preStmt.setString(7, mapName);
        	preStmt.setInt(8, gender);
        	
        	preStmt.setBigDecimal(9, id);
        	
        	if(preStmt.executeUpdate() > 0){
        		result = true;
        	}
        } catch (Exception ex) {
        	logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
        return result;
	}
	
	/**
	 * function update avatar of player
	 * @param id
	 * @param bAvatar
	 * @return true if update success else return false.
	 */
	public boolean updateRow(BigDecimal id, byte[] bAvatar){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_PLAYER set AVATAR = ?, LAST_UPDATED = sysdate where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBytes(1, bAvatar);
			preStmt.setBigDecimal(2, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}	

	/**
	 * function delete a TennisPlayer
	 * @param id
	 * @return true if update success else return false.
	 */
	public boolean deleteRow(BigDecimal id){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" delete from TENNIS_PLAYER where ID = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function get avatar of member
	 * @param id
	 * @return byte[]
	 */
    public byte[] getAvatar(BigDecimal id) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	byte[] result = null;
    	try {
    		conn = poolX.getConnection();
    		strSQL = "select AVATAR from TENNIS_PLAYER "
    			+ " where ID = ?";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, id);
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			Blob bAvatar = rs.getBlob(1);
    			if (bAvatar != null) {
    				try {
    					result = LOBs.readByteBlob(bAvatar);
    				} catch (Exception e) {
    					logger.error("getAvatar: Error executing SQL " + strSQL + ">>>" + e.toString());
    				}
    			}
    		}    		
    	} catch (SQLException e) {
    		logger.error("getAvatar: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	
    	return result;
    	
    }
}
